package com.kele.kettle;

import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.row.RowMeta;
import org.pentaho.di.core.row.RowMetaInterface;
import org.pentaho.di.core.row.ValueMetaInterface;
import org.pentaho.di.repository.kdr.KettleDatabaseRepository;
import org.pentaho.di.repository.kdr.KettleDatabaseRepositoryMeta;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransHopMeta;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.steps.exceloutput.ExcelField;
import org.pentaho.di.trans.steps.exceloutput.ExcelOutputMeta;
import org.pentaho.di.trans.steps.tableinput.TableInput;
import org.pentaho.di.trans.steps.tableinput.TableInputMeta;
import sun.net.TelnetInputStream;

/**
 * @title：
 * @author: kele
 * @date: 2021年02月02日 15:49
 * @description:
 */
public class KettleExcelDemo extends ExcelOutputMeta {




    public static KettleDatabaseRepository KettleExcelDemo() throws KettleException {

        if (!KettleEnvironment.isInitialized()){
            KettleEnvironment.init();
        }
        /**
        数据库元连接
        参数介绍
        kettle资源库名称，数据库类型，连接类型，ip，数据库名称，端口，用户名，密码
         */
        DatabaseMeta databaseMeta = new DatabaseMeta("excelTest", "mysql", "Native(JDBC)", "192.168.241.1", "demo?useUnicode=true&characterEncoding=utf-8&useSSL=false",
                "3306", "root", "Aki@lch");

        //设置kettle资源库以数据库形式的资源库元对象
        KettleDatabaseRepositoryMeta repository = new KettleDatabaseRepositoryMeta();
        //保存数据库连接至元对象种
        repository.setConnection(databaseMeta);
        //数据库形式的资源库对象
        KettleDatabaseRepository databaseRepository = new KettleDatabaseRepository();
        //用资源库元对象初始化资源库对象
        databaseRepository.init(repository);
        //连接到资源库
        databaseRepository.connect("admin","admin");
        //判断状态为false | true
        if (databaseRepository.isConnected()) {
            System.out.println("连接成功，资源库名称为：" + databaseRepository.getDatabaseMeta());
            return databaseRepository;
        } else {
            System.out.println("连接失败，资源库名称为：" + databaseRepository.getDatabaseMeta());
            return null;
        }
    }

    public static void main(String[] args) throws KettleException {
        //获得资源库对象
        KettleDatabaseRepository repository=KettleExcelDemo();
        //创建转换对象
        TransMeta transMeta=new TransMeta();
        //设置转换资源库的名称
        transMeta.setRepository(repository);
        //设置转换的名称
        transMeta.setName("excelTrans");

        RowMetaInterface row=new RowMeta();
        //String[] fieldNames = row.getFieldNames();

        //创建转换的保存的位置
        assert repository != null;
        transMeta.setRepositoryDirectory(repository.findDirectory("/"));
        //添加数据源
        transMeta.addDatabase(repository.getDatabaseMeta());
        //添加表输入组价
        TableInputMeta inputMeta=new TableInputMeta();
        //设置默认值
        inputMeta.setDefault();
        //设置数据源
        inputMeta.setDatabaseMeta(transMeta.findDatabase("excelTest"));
        //sql语句
        inputMeta.setSQL("select * from student");
        //创建输入步骤实例
        StepMeta stepMetaInput=new StepMeta("表输入步骤",inputMeta);
        //将步骤加到作业
        transMeta.addStep(stepMetaInput);

        //添加excel输出组件
        ExcelOutputMeta outputMeta=new ExcelOutputMeta();

        ExcelField[] excelFileds = getExcelFileds(row);
        //设置格式
        outputMeta.setExtension("xls");
        //设置头部是否输出到excel也就是字段名是否输出到excel每一列的标题
        outputMeta.setHeaderEnabled(true);
        outputMeta.setOutputFields(excelFileds);
        //设置位置
        outputMeta.setFileName("E:\\kettleSourceCode\\excel\\kettleSourceCode");
        outputMeta.setEncoding("GBK");


        //创建输出步骤实例
        StepMeta stepMetaOutput=new StepMeta("输出步骤",outputMeta);
        //将步骤加到作业中
        transMeta.addStep(stepMetaOutput);

        //连接对象，讲输入输出步骤连接起来
        TransHopMeta hopMeta=new TransHopMeta(stepMetaInput,stepMetaOutput);
        //添加转换中
        transMeta.addTransHop(hopMeta);

        //保存该转换到资源库
        repository.save(transMeta,null);
        //把转换放入到trans
        Trans trans=new Trans(transMeta);
        //开始执行
        trans.execute(null);
        //等待执行完毕
        trans.waitUntilFinished();

    }

    private static ExcelField[] getExcelFileds(RowMetaInterface row) {
        ExcelField [] excelFields=new ExcelField[row.size()];
        for (int i=0;i<row.size();i++){
            ValueMetaInterface field=row.getValueMeta(i);
            ExcelField excelField=new ExcelField();
            excelField.setName(field.getName());
            excelField.setType(field.getType());
            if (excelField.getType()==5){
                excelField.setFormat("0");
            }
            excelFields[i]=excelField;

        }
        return  excelFields;
    }


}
